#!/usr/bin/perl -w
use warnings;
use Mail::Sender;
use Net::SMTP;
use DBI;

#连接数据据库
my $driver="DBI:mysql";
my $database="news";
my $host="localhost";
my $user="root";
my $password="";
my $db=DBI->connect("$driver:database=$database;host=$host;user=$user;password=$password");

#定义参数
my $email;
my $mail_info;#拼接table;
my $my_count;#计算发送邮件漏洞的总条数
my $email_count;#发邮件的次数
my $qun_id;
my $g_title;
my $g_author;
my $g_web;
my $qun_nid;
my $qun_name;
my $check_status;
my $all_status;
my @newsid=();
my @files=();
my $send_status;
my $email_title;
my $null_email;

#定时发送
my $sth=$db->prepare("select * from mygroup where send_time<now()");#得到今天需要发邮件的群
$sth->execute();
while(@recs=$sth->fetchrow_array){
       @newsid=();
	   @files=();
	   $my_count=0;
       $qun_id=$recs[0];
	   $qun_name=$recs[1];
	   $send_status=$recs[2];
	   $check_status=$recs[4];
	   $g_title=$recs[5];
	   $g_author=$recs[6];
	   $g_web=$recs[7];
	   $qun_nid=$recs[8];
       #得到群的email_count值
	   $email_count=$recs[9];
	   $email_title=$recs[10];
	   $null_email=$recs[11];
	   $email_count2=$email_count+1;
	   if($send_status eq 0){#是定时发送
			if($email_count eq 0){
		                ##更新check_status状态, 改为待发送
						$update_statue=$db->prepare("update mygroup set check_status=1 where id=$qun_id;");
						$update_statue->execute();
						#调用方法,发送邮件
						&email_to();
						##更新时间与$email_count
						$update_time=$db->prepare("update  mygroup set send_time=DATE_ADD(send_time,INTERVAL 510 MINUTE),email_count=$email_count2  where id=$qun_id;");
						$update_time->execute();
			}
			
			if($email_count eq 1){
				##更新check_status状态,改为待发送
				$update_statue=$db->prepare("update mygroup set check_status=1 where id=$qun_id;");
				$update_statue->execute();
				&email_to();
				##更新时间
				$update_time=$db->prepare("update  mygroup set send_time=DATE_ADD(send_time,INTERVAL  1   DAY) where id=$qun_id;");
				 $update_time->execute();
				$update_time=$db->prepare("update  mygroup set send_time=DATE_ADD(send_time,INTERVAL  -510  MINUTE),email_count=0 where id=$qun_id;");
				$update_time->execute();
			}
			
			##更新check_status状态, 
			$update_statue=$db->prepare("update mygroup set check_status=2 where id=$qun_id;");
			$update_statue->execute();
			
			#更新群中的nid,防止重复的发送
			my $max=$newsid[0];
			my $line=scalar(@newsid);
			for(my $i=0;$i<$line;$i++){
	
				if($max<$newsid[$i]){
					$max=$newsid[$i];
				}
				
			}
			
			if($max ne ""){
			
				$update=$db->prepare("update mygroup set nid=$max where id=$qun_id");
				$update->execute();
				
			}

	   }
	  
	  
}

#及时发送
my $sth2=$db->prepare("select * from mygroup;");
$sth2->execute();
while(@recs2=$sth2->fetchrow_array){
       @newsid=();
	   @files=();
	   $my_count=0;
       $qun_id=$recs2[0];
	   $qun_name=$recs2[1];
	   $send_status=$recs2[2];
	   $check_status=$recs2[4];
	   $g_title=$recs2[5];
	   $g_author=$recs2[6];
	   $g_web=$recs2[7];
	   $qun_nid=$recs2[8];
       #得到群的email_count值
	   $email_title=$recs2[10];
	   $null_email=$recs2[11];
	   
	   if($send_status eq 1){#即时发送
			 ##更新check_status状态, 改为待发送
			 $update_statue=$db->prepare("update mygroup set check_status=1 where id=$qun_id;");
			 $update_statue->execute();
			 #调用方法,发送邮件
			 &email_to();
			 ##更新check_status状态, 
			 $update_statue=$db->prepare("update mygroup set check_status=2 where id=$qun_id;");
			 $update_statue->execute();
			 #更新群中的nid,防止重复的发送
			 my $max=$newsid[0];
			 my $line=scalar(@newsid);
			 for(my $i=0;$i<$line;$i++){
			
				  if($max<$newsid[$i]){
					  $max=$newsid[$i];
				  }
				  
			 }
			 if($max ne ""){
				$update=$db->prepare("update mygroup set nid=$max where id=$qun_id");
				$update->execute();
			 }
			
		   
	   }

}

#得到每一个受众
sub email_to(){
	#查出每个群需要发送的新闻
	$mail_info="";#拼接table的部分
	my @title_array=();
	@title_array=split("\n",$g_title);
	my $strt1=" (title like '%$title_array[0]%'";
	my $strt2;
	for(my $i=1;$i<=$#title_array;$i++){
	 $strt2.=" or title like '%$title_array[$i]%'";
	}
	my $rel_title=$strt1.$strt2.")";#title
	my @author_array=();
    @author_array=split("\n",$g_author);
	
	my $stra1=" and (author like '%$author_array[0]%'";
	my $stra2="";
	for(my $i=1;$i<=$#author_array;$i++){
	  $stra2.=" or author like '%$author_array[$i]%'";
	} 
	my $rel_author=$stra1.$stra2.")";#author
	my @web_array=();
	@web_array=split(",",$g_web);
	my $strw1=" and (web=$web_array[0]";
	my $strw2;
	for(my $i=1;$i<=$#web_array;$i++){
		$strw2.=" or web=$web_array[$i]";
	} 
	my $rel_web=$strw1.$strw2.")";#web
	my $rel_sql=$rel_title.$rel_author.$rel_web; 
	my $ss="select * from news where".$rel_sql;
	my $news_all=$db->prepare("select * from news where".$rel_sql);
	$news_all->execute();
    
	while(@recs_news=$news_all->fetchrow_array){
		
		  my $id=$recs_news[0];
		  my $title=$recs_news[1];
		  my $web=$recs_news[2];
		  #根据web得到真正的网站
		  my $rel_web=$db->prepare("select * from wangzhan where id=$web");
		  $rel_web->execute();
		  @rel_web2=$rel_web->fetchrow_array;
		  my $rel_web3=$rel_web2[1];
		   my $rel_web4;
		   
		  if($web==6){
		  
		    $rel_web4="exploit-database";
			
		  }else{
		  
		       $rel_web3=~s/\/\//\//g;
			   my @rel_web3=split(/\//,$rel_web3);
		       $rel_web4=$rel_web3[1];
		  
		  }
		  
		  my $link=$recs_news[6];
		  my $publishtime=$recs_news[4];
		  my $publishtime2=substr($publishtime,0,10);
		  my $file_name=$recs_news[5];#content字段(存文件名)
		  my $my_web=$recs_news[2];
	
	      if($qun_nid<$id){
		  
		      $my_count++;
			  $mail_info.="<tr align='center'> <td height='20' bgcolor='#FFFFFF'><a href='$link' style='text-decoration:none;color:#0099cc;'>$title</a></td>
						   <td height='20' bgcolor='#FFFFFF'>$rel_web4</td>
						 
						   </tr>";
				   
			  push(@newsid,$id);
			  #mysql中的content字段存的是文件名
			  if($my_web eq 6){
				  push(@files,$file_name);
			  }
		  
		  }
		  
		  

	}#新闻遍历完毕
	 my $sth2=$db->prepare("select * from guanxi where gid=$qun_id");
	 $sth2->execute();
	 while(@recs2=$sth2->fetchrow_array){
		
		my $sid=$recs2[1];
		my $sql="select * from shouzhong where id=$sid";
		my $sth3=$db->prepare("select * from shouzhong where id=$sid");
		$sth3->execute();
		@shouzhong=$sth3->fetchrow_array;
		my $type=$shouzhong[2];
		$email=$shouzhong[3];
		my $nid=$shouzhong[6];
		
		if($type eq 0){
		     my $flag=1;
			 if($my_count eq 0){
				if($send_status eq 1){#为及时发送
					$flag=0;
				}
				
				if($null_email eq 1){#勾选是否接受空邮箱
				   $flag=0;
				}
				
			 }
			if($flag eq 1){
			    #发邮件前:把文件打成tar包,便于发送附件
				my $files_num=scalar @files;
				if($files_num ne 0){#有需要发送附件的
					   my $shanchu_dabao_lm="/var/www/html2/send_email/loudong/";
					   my $sh_db=`rm -rf $shanchu_dabao_lm`;
					   my $cj_dabao=`cd /var/www/html2/send_email/&& mkdir loudong`;#创建文件
					   my $qunxian=`cd /var/www/html2/send_email/ && chmod 777 loudong`;#修改权限
					  for(my $i=0;$i<$files_num;$i++){
					  
							 my $yd_file=$files[$i];
							 my $cp=`cp /var/www/html2/wangzhan/wenjian/$yd_file /var/www/html2/send_email/loudong/$yd_file`;

					 }
				
						       #将dabao文件夹打成tar包发送出去
						        my $tar_dabao=`cd /var/www/html2/send_email/&& tar -zcvf loudong.tar.gz loudong/`;
						   
						
				}
				
			    &send_email();
				
			}
			 
		
		}
		
	 }

	 
}

#邮件
sub send_email{

my $guanyu="";
#对涉及范围的的定义
if($g_title ne ""){
   $guanyu.='<DIV style="MARGIN-BOTTOM: 1.5em;line-height:1.2em">涉及标题:'.$g_title.'</DIV><br>';
}
if($g_author ne ""){

   $guanyu.='<DIV style="MARGIN-BOTTOM: 0.5em">涉及作者:'.$g_author.'</DIV><br>';

}

if($g_web ne ""){

        my $yuan_wangzhan='<DIV style="MARGIN-BOTTOM: 1.5em;line-height:1.2em">涉及网站:';
        my @web_array2=();
		@web_array2=split(",",$g_web);
		my $strw1_1=" id=$web_array2[0]";
		my $strw2_2;
		
		for(my $i=1;$i<=$#web_array2;$i++){
			$strw2_2.=" or id=$web_array2[$i]";
		}
		my $rel_web2=$strw1_1.$strw2_2;#web
		#my $sql="select * from wangzhan where".$rel_web2;
		#print $sql;
     	my $guan_wang=$db->prepare("select * from wangzhan where".$rel_web2);
        $guan_wang->execute();
		
		while(@guan_wang2=$guan_wang->fetchrow_array){
		     my $web_zj3;
		     my $web_zj=$guan_wang2[1];
			 if($web_zj=~/\/\//){
			    $web_zj=~s/\/\//\//g;
			    my @web_zj2=split(/\//,$web_zj);
			    $web_zj3=$web_zj2[1];
			 }else{
			     
				 $web_zj3="exploit-database";
				 
			 }
			 
			 $yuan_wangzhan.="   ".$web_zj3;
			  
		}
		$guanyu.=$yuan_wangzhan."</DIV><br>";
		
}

my $mailheader = <<HTML_HEAD;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" contect="text/html;charset=utf-8">
<head>
<TABLE border=0 cellSpacing=0 cellPadding=0 width="100%" align=center>
<TBODY>
<TR>
<TD style="PADDING-BOTTOM: 40px; BACKGROUND-COLOR: #f2f2f2; PADDING-LEFT: 20px; PADDING-RIGHT: 20px; PADDING-TOP: 40px">
<TABLE style="MARGIN: 0px auto" border=0 cellSpacing=0 cellPadding=0 width=660 align=center>
<TBODY>
<TR>
<TD align=left>
<DIV style="MARGIN-BOTTOM: 0.4em"></DIV>
<DIV style="BORDER-BOTTOM: #d5d5d5 1px solid; BORDER-LEFT: #d5d5d5 1px solid; BACKGROUND-COLOR: #fff; FONT-FAMILY: 微软雅黑, 宋体; FONT-SIZE: 14px; BORDER-TOP: #d5d5d5 1px solid; BORDER-RIGHT: #d5d5d5 1px solid">
<DIV style="PADDING-BOTTOM: 1em; BACKGROUND-COLOR: #0099cc; PADDING-LEFT: 1.5em; PADDING-RIGHT: 1.5em; COLOR: #fff; PADDING-TOP: 1em">
<DIV style="FONT-SIZE: 36px">
<H3 align=center><FONT size=6>$email_title</FONT></H3></DIV></DIV>
<DIV style="PADDING-BOTTOM: 1.5em; LINE-HEIGHT: 0.5; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 1.5em">
<DIV style="PADDING-BOTTOM: 0px; PADDING-LEFT: 1.5em; PADDING-RIGHT: 1.5em; PADDING-TOP: 0px">

HTML_HEAD

my $basic_info=<<HTML_BASIC_INFO;
<DIV style="MARGIN-BOTTOM: 1em; FONT-WEIGHT: bold"> 亲爱的$email：</DIV>
<DIV style="MARGIN-BOTTOM: 0.5em">您好！欢迎使用新闻订阅中心，</DIV>
<h3>【基本信息】</h3>
<DIV style="MARGIN-BOTTOM: 0.5em">共计$my_count 条新闻</DIV>
<br>$guanyu
<DIV style="MARGIN-BOTTOM: 0.5em">
HTML_BASIC_INFO
my $tail_info=<<HTML_VUL_INFO_BEGIN;
<br/></br/><DIV style="MARGIN-BOTTOM: 0.5em">此为系统邮件，请勿直接回复</DIV>
HTML_VUL_INFO_BEGIN


#中间表格的设计
my $mail_info_head="<table width='100%' border='0' cellpadding='0' cellspacing='1' bgcolor='#a8c7ce' >
      <tr align='center' align='center'>
        <td width='50%' height='20' bgcolor='d3eaef'><div align='center'><span class='STYLE10'>标题</span></div></td>
        <td width='25%' height='20' bgcolor='d3eaef'><div align='center'><span class='STYLE10'>来源网站</span></div></td>
      </tr>";

my $mail_info_wei="</table>";

my $mailbody=$mailheader;
my $mail_info_quan="";
if($my_count eq 0){

    $mail_info_quan="<p>暂时无新闻</p>";
	
}else{
    $mail_info_quan=$mail_info_head.$mail_info.$mail_info_wei;

}


$mailbody .=$basic_info;
$mailbody .=$mail_info_quan;
$mailbody .=$tail_info;
$mailbody .="</body></html>";


#得到当前的时间
my $cur_time=$db->prepare("select now();");#得到今天需要发邮件的群
$cur_time->execute();
@cur_time2=$cur_time->fetchrow_array;
my $rel_time=$cur_time2[0];

my $rel_year=substr($rel_time,0,4);
my $rel_mon=substr($rel_time,5,2);
my $rel_day=substr($rel_time,8,2);
my $rel_shijian=substr($rel_time,11,8);


use MIME::Base64;
my $emailsubject=$email_title.$rel_year."年".$rel_mon."月".$rel_day."日(".$rel_shijian.")";
$title="=?UTF-8?B?".encode_base64($emailsubject)."?=";


#查数据库得到有关邮箱的信息
my $email_xinxi=$db->prepare("select * from email_new");
$email_xinxi->execute();
@email_xinxi2=$email_xinxi->fetchrow_array;
my $from_serer=$email_xinxi2[1];#服务器地址
my $from_email=$email_xinxi2[2];#发送邮箱的用户名
my $pass=$email_xinxi2[3];#发送邮箱的密码
my $cesi_email=$email_xinxi2[4];#测试邮箱

my $to =$email;
my $pbody = $mailbody;


my $sender = new Mail::Sender {
	smtp => $from_serer,
	fake_from =>"from",
	from => $from_email,
	debug => undef,
	auth => "LOGIN",
	#charset => "utf8",
	authid => $from_email,
	authpwd =>$pass,
	boundary=> '----this-is-a-mail-boundary-----'
};

die  "Can't create the Mail::Sender object: $Mail::Sender::Error"  unless ref $sender;


$sender->OpenMultipart({to => $to,
		 cc => undef,
		 replyto =>$from_email,
		 fake_from => $from_email,
		 ctype=>'text/html',
		 subject => $title});
$sender->Part({ctype =>'text/html',msg =>$pbody });
#$sender->Body({charset=>"utf8", msg => $$pbody});

my $pd_wenjian="/var/www/html2/send_email/loudong.tar.gz";

if(-e $pd_wenjian){#文件不存在
    my @file_arr=("loudong.tar.gz");
	foreach my $filename ( @file_arr ) {
	$sender->Attach({ description=>$filename,
			ctype=>'text/plain',
			disposition=>"attachment;filename=$filename",
			file=>$filename
			}) || die "Error in attachment: $Mail::Sender::Error\n";
			
	#发送完成之后需要删除附件的压缩包，以免下次发送重复的
    my $shanchu=`cd /var/www/html2/send_email/ && rm -f loudong.tar.gz`;
	
   }
  
}

$sender->Close;

}
